package com.ce.common.db.base;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


/**
 * 设置jdbc连接, 用于最基本的查询
 * @author yangenxiong
 *
 */
public class JDBCConnection implements DBConnection {

	private static DBConnection dbConn;

	private Connection conn;

	private Statement stemt;

	/**
	 * 用于获取DBConnection对象
	 * @return
	 * @throws Exception
	 */
	public static DBConnection getJDBCConnection() throws Exception {
		if (dbConn == null) {
			dbConn = new JDBCConnection();
		}
		return dbConn;
	}

	private JDBCConnection() throws Exception {
		Class.forName(PropertiesUtil.JDBC_DRIVER);
		conn = DriverManager.getConnection(PropertiesUtil.JDBC_URL_NO_CHECK,
				PropertiesUtil.JDBC_USERNAME, PropertiesUtil.JDBC_PASSWD);
		stemt = conn.createStatement();
	}
	
	/**
	 * 执行若干句sql语句, 这些语句存在同一个事务中
	 * @author yangenxiong
	 * @param sql 需要执行的sql语句的集合
	 * @return true 全部语句执行成功 false 全部执行失败
	 */
	public boolean execute(List<String> sql) {
		boolean result = true;
		try {
			conn.setAutoCommit(false);
			for (int i = 0; i < sql.size(); i++) {
				stemt.execute(sql.get(i));
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {//当有某个sql执行失败时, 抛出异常
			//e.printStackTrace();
			result = false;
			
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		
		return result;
	}

	/**
	 * 执行一句sql语句
	 * @author yangenxiong
	 * @param sql 需要执行的sql语句
	 * @return true 执行成功 false 执行失败
	 */
	public boolean execute(String sql) throws Exception {
		boolean result = true;
		try {
			result = stemt.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}
		return result;
	}


	/**
	 * 执行最基本的查询
	 * @author yangenxiong
	 */
	public ResultSet query(String sql) throws Exception {
		return stemt.executeQuery(sql);
	}

	/**
	 * 查询参数表名的外键关系
	 * @author yangenxiong
	 */
	public ResultSet queryRelations(String tableName) throws Exception {
		return conn.getMetaData().getImportedKeys(null, null, tableName);
	}

	/**
	 * 查询所有的表和视图, 返回ResultSet
	 * @author yangenxiong
	 */
	public ResultSet queryTables(String[] types) throws Exception {
		return conn.getMetaData().getTables(null, null, null,
				types);
	}

	/**
	 * 关闭ResultSet的方法
	 * @param 需要关闭的ResultSet
	 */
	public boolean closeResultSet(ResultSet rs) {
		try {
			rs.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 用于关闭连接
	 * @return
	 */
	public boolean closeConnection() {
		try {
			conn.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
	}
}
